import pymysql
class MySQLHelper:
    def __init__(self, host, port, user, password, db, charset='utf8'):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db = db
        self.charset = charset
        self.conn = None
        self.cursor = None
    def connect(self):
        self.conn = pymysql.Connect(host=self.host, port=self.port,
                                    user=self.user, password=self.password,
                                    db=self.db, charset=self.charset)
        self.cursor = self.conn.cursor()

    def execute_select(self, sql):
        self.connect()
        self.cursor.execute(sql)
        ret = self.cursor.fetchall()
        data = []
        for k in ret:
            target = {}
            for j in range(len(self.cursor.description)):
                target[self.cursor.description[j][0]] = k[j]
            data.append(target)
        self.close()
        return data

    def execute_edit(self, sql):
        self.connect()
        ret = self.cursor.execute(sql)
        self.conn.commit()
        self.close()
        return ret

    def execute_charts(self, sql):
        self.connect()
        self.cursor.execute(sql)
        ret = self.cursor.fetchall()
        target = {}
        for j in range(len(self.cursor.description)):
            data = []
            for k in ret:
                data.append(k[j])
            target[self.cursor.description[j][0]] =data
        self.close()
        return target

    def execute_datatable(self, sql):
        self.connect()
        self.cursor.execute(sql)
        ret = self.cursor.fetchall()
        datas = []
        for k in ret:
            target = []
            for j in range(len(self.cursor.description)):
                target.append( k[j])
            datas.append(target)
        data = {"data":datas}
        self.close()
        return data

    def execute_cloud(self, sql):
        self.connect()
        self.cursor.execute(sql)
        ret = self.cursor.fetchall()
        data={}
        for k in ret:
            data[k[0]]=k[1]
        self.close()
        return data

    def close(self):
        if self.cursor is not None:
            self.cursor.close()
        if self.conn is not None:
            self.conn.close()



if __name__=="__main__":
    # 创建 MySQLHelper 实例对象
    db = MySQLHelper(host='180.178.36.194', port=3306,
                     user='sqlyanshujuku', password='zwj798261',
                     db='sqlsqlyanshujuku', charset='utf8')
    # 执行 SELECT 查询语句
    data = db.execute_charts("SELECT * FROM 房间表")
    # 输出查询结果
    print(data)
